package org.pictual.business.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.ResourceBundle;

import org.apache.log4j.Logger;
import org.pictual.business.dao.exceptions.DAOException;
import org.pictual.business.vo.PalabraVO;


public class MySqlPalabraDAO implements PalabraDAO{
	
	private Connection connection;
	private ResourceBundle queries=ResourceBundle.getBundle("org.pictual.business.dao.MySqlPalabraDAO");
	protected static Logger log= Logger.getLogger(MySqlPalabraDAO.class);
	
	public MySqlPalabraDAO(){}
	
	public MySqlPalabraDAO(Connection connection){
		this.connection=connection;
	}
	
	/**
	 * AccesoBD
	 */
	public PalabraVO readPalabra(int palabra_id) throws DAOException {
		AccesoBD accesoBD = new AccesoBD();
		ResultSet rs=null;
		PalabraVO palabraVO = null;
		final String query=queries.getString("readPalabra");
		
		try {
			rs = accesoBD.ejecutarConsulta(
					query, 
					palabra_id);
			
			if(rs!=null){
			
				while(rs.next()){
					palabraVO=new PalabraVO();
										
					palabraVO.setPalabra_id(rs.getInt("palabra_id"));
					palabraVO.setImagen_1(rs.getInt("imagen_1"));
					palabraVO.setImagen_2(rs.getInt("imagen_2"));
					palabraVO.setImagen_3(rs.getInt("imagen_3"));
					palabraVO.setImagen_4(rs.getInt("imagen_4"));
					palabraVO.setImagen_5(rs.getInt("imagen_5"));
					palabraVO.setImagen_6(rs.getInt("imagen_6"));
					palabraVO.setImage_old_1(rs.getInt("imagen_old_1"));
					palabraVO.setImage_old_2(rs.getInt("imagen_old_2"));
					palabraVO.setImage_old_3(rs.getInt("imagen_old_3"));
					palabraVO.setImage_old_4(rs.getInt("imagen_old_4"));
					palabraVO.setImage_old_5(rs.getInt("imagen_old_5"));
					palabraVO.setImage_old_6(rs.getInt("imagen_old_6"));
					
					log.info("Obtencion de PalabraVO de"+palabraVO.getPalabra_id()+" de base de datos con exito");
				}
			}
			
		} catch (SQLException e) {
			log.error("Error al ejecutar: "+query, e);
			throw new DAOException(query, e);
		} finally{
			accesoBD.desconectar();
			accesoBD.cerrarResultSet(rs);
		}
		
		return palabraVO;
	}
	
	/**
	 * @deprecated
	 * @param palabra_id
	 * @return
	 * @throws DAOException
	 */
	public PalabraVO readPalabra2(int palabra_id) throws DAOException {
		ResultSet rs=null;
		PreparedStatement ps=null;
		PalabraVO palabraVO = null;
		final String query=queries.getString("readPalabra");
		try {
			if(null == ps){ 
				ps= connection.prepareStatement(query);
				ps.setInt(1, palabra_id);
				rs = ps.executeQuery();
				while(rs.next()){
					palabraVO=new PalabraVO();
										
					palabraVO.setPalabra_id(rs.getInt("palabra_id"));
					palabraVO.setImagen_1(rs.getInt("imagen_1"));
					palabraVO.setImagen_2(rs.getInt("imagen_2"));
					palabraVO.setImagen_3(rs.getInt("imagen_3"));
					palabraVO.setImagen_4(rs.getInt("imagen_4"));
					palabraVO.setImagen_5(rs.getInt("imagen_5"));
					palabraVO.setImagen_6(rs.getInt("imagen_6"));
					palabraVO.setImage_old_1(rs.getInt("imagen_old_1"));
					palabraVO.setImage_old_2(rs.getInt("imagen_old_2"));
					palabraVO.setImage_old_3(rs.getInt("imagen_old_3"));
					palabraVO.setImage_old_4(rs.getInt("imagen_old_4"));
					palabraVO.setImage_old_5(rs.getInt("imagen_old_5"));
					palabraVO.setImage_old_6(rs.getInt("imagen_old_6"));
					
					log.info("Obtencion de PalabraVO de"+palabraVO.getPalabra_id()+" de base de datos con exito");
				}
			}

		} catch (SQLException e) {
			log.error("Error al ejecutar: "+query, e);
			throw new DAOException(query, e);
		} finally {
			try {
				rs.close();
				ps.close();
				connection.close();
			} catch (SQLException e) {
				log.error("Error al cerrar la consulta: "+query, e);
			}
		}
		
		return palabraVO;
	}

	/**
	 * AccesoBD
	 */
	public void insertPalabra(PalabraVO palabraVO) throws DAOException {
		AccesoBD accesoBD = new AccesoBD();

		String query =queries.getString("insertPalabra");
		
		try {
			accesoBD.ejecutarActualizacion(
					query, 
					palabraVO.getPalabra_id(),
					palabraVO.getImagen_1(),
					palabraVO.getImagen_2(),
					palabraVO.getImagen_3(),
					palabraVO.getImagen_4(),
					palabraVO.getImagen_5(),
					palabraVO.getImagen_6(),
					palabraVO.getImage_old_1(),
					palabraVO.getImage_old_2(),
					palabraVO.getImage_old_3(),
					palabraVO.getImage_old_4(),
					palabraVO.getImage_old_5(),
					palabraVO.getImage_old_6()
					);
		} catch (SQLException e) {
			log.error("Error al ejecutar: "+query, e);
			throw new DAOException(query, e);
		} finally{
			accesoBD.desconectar();
		}
	}
	/**
	 * @deprecated
	 */
	public void insertPalabra2(PalabraVO palabraVO) throws DAOException {
		String query =queries.getString("insertPalabra");
		PreparedStatement ps=null;
		try {
			if(null == ps){
				ps=connection.prepareStatement(query);
				
				ps.setInt(1, palabraVO.getPalabra_id());
				ps.setInt(2, palabraVO.getImagen_1());
				ps.setInt(3, palabraVO.getImagen_2());
				ps.setInt(4, palabraVO.getImagen_3());
				ps.setInt(5, palabraVO.getImagen_4());
				ps.setInt(6, palabraVO.getImagen_5());
				ps.setInt(7, palabraVO.getImagen_6());
				ps.setInt(8, palabraVO.getImage_old_1());
				ps.setInt(9, palabraVO.getImage_old_2());
				ps.setInt(10, palabraVO.getImage_old_3());
				ps.setInt(11, palabraVO.getImage_old_4());
				ps.setInt(12, palabraVO.getImage_old_5());
				ps.setInt(13, palabraVO.getImage_old_6());
				
				ps.executeUpdate();
				log.info("Inserción de PalabraVO de"+palabraVO.getPalabra_id()+" en base de datos con exito");
			}
		} catch (SQLException e) {
			log.error("Error al ejecutar: "+query, e);
			throw new DAOException(query, e);
		}  finally {
			try {
				ps.close();
				connection.close();
			} catch (SQLException e) {
				log.error("Error al cerrar la consulta: "+query, e);
			}
		}
	}

	
	public void updatePalabra(PalabraVO palabraVO) throws DAOException {
		AccesoBD accesoBD = new AccesoBD();
		final String query=queries.getString("updatePalabra");
//		System.out.println(query);
		try {
			accesoBD.ejecutarActualizacion(
					query,
					palabraVO.getImagen_1(),
					palabraVO.getImagen_2(),
					palabraVO.getImagen_3(),
					palabraVO.getImagen_4(),
					palabraVO.getImagen_5(),
					palabraVO.getImagen_6(),
					palabraVO.getImage_old_1(),
					palabraVO.getImage_old_2(),
					palabraVO.getImage_old_3(),
					palabraVO.getImage_old_4(),
					palabraVO.getImage_old_5(),
					palabraVO.getImage_old_6(),
					palabraVO.getPalabra_id()
					);
			
			log.info("Actualización de PalabraVO de"+palabraVO.getPalabra_id()+" en base de datos con exito");

//			System.out.println(palabraVO);
		} catch (SQLException e) {
			log.error("Error al ejecutar: "+query, e);
			throw new DAOException(query, e);
		} finally{
//			try {
//				accesoBD.commit();
//			} catch (SQLException e) {
//				log.error("Error al hacer commit: "+query, e);
//			}
			accesoBD.desconectar();
		}
	}
	
	/**
	 * @deprecated
	 */
	public void updatePalabra2(PalabraVO palabraVO) throws DAOException {
		PreparedStatement ps=null;
		final String query=queries.getString("updatePalabra");
		try {
			if(null == ps){ 
				ps= connection.prepareStatement(query);

				ps.setInt(1, palabraVO.getImagen_1());
				ps.setInt(2, palabraVO.getImagen_2());
				ps.setInt(3, palabraVO.getImagen_3());
				ps.setInt(4, palabraVO.getImagen_4());
				ps.setInt(5, palabraVO.getImagen_5());
				ps.setInt(6, palabraVO.getImagen_6());
				ps.setInt(7, palabraVO.getImage_old_1());
				ps.setInt(8, palabraVO.getImage_old_2());
				ps.setInt(9, palabraVO.getImage_old_3());
				ps.setInt(10, palabraVO.getImage_old_4());
				ps.setInt(11, palabraVO.getImage_old_5());
				ps.setInt(12, palabraVO.getImage_old_6());
				ps.setInt(13, palabraVO.getPalabra_id());
				
				ps.executeUpdate();
				log.info("Actualización de PalabraVO de"+palabraVO.getPalabra_id()+" en base de datos con exito");
				
			}

		} catch (SQLException e) {
			log.error("Error al ejecutar: "+query, e);
			throw new DAOException(query, e);
		} finally {
			try {
				ps.close();
				connection.close();
			} catch (SQLException e) {
				log.error("Error al cerrar la consulta: "+query, e);
			}
		}
		
	}

	public boolean haveImages(int palabra_id) throws DAOException {
		AccesoBD accesoBD = new AccesoBD();
		final String query=queries.getString("haveImages");
		ResultSet rs=null;
		boolean haveImages = true;
		
		try {
			rs = accesoBD.ejecutarConsulta(
					query, 
					palabra_id);
			
			if(rs!=null){
				while(rs.next()){
					log.info("La palabra con id "+palabra_id+" no tiene imagenes");
					haveImages = false;
				}
			}
			
			
		} catch (SQLException e) {
			log.error("Error al ejecutar: "+query, e);
			throw new DAOException(query, e);
		} finally{
			accesoBD.desconectar();
			accesoBD.cerrarResultSet(rs);
		}
		
		return haveImages;
	}

	/**
	 * @deprecated
	 * @param palabra_id
	 * @return
	 * @throws DAOException
	 */
	public boolean haveImages2(int palabra_id) throws DAOException {
		ResultSet rs=null;
		PreparedStatement ps=null;
		final String query=queries.getString("haveImages");
		boolean haveImages = true;
		try {
			if(null == ps){ 
				ps= connection.prepareStatement(query);
				ps.setInt(1, palabra_id);
				rs = ps.executeQuery();
				while(rs.next()){
					log.info("La palabra con id "+palabra_id+" no tiene imagenes");
					haveImages = false;
				}
			}

		} catch (SQLException e) {
			log.error("Error al ejecutar: "+query, e);
			throw new DAOException(query, e);
		} finally {
			try {
				rs.close();
				ps.close();
//				connection.close();
			} catch (SQLException e) {
				log.error("Error al cerrar la consulta: "+query, e);
			}
		}
		
		return haveImages;
	}

	@Override
	public List<Integer> withoutImages() throws DAOException {
		// TODO Auto-generated method stub
		return null;
	}

	

	
}
